import csv
import pandas as pd
import re
import os

def ditc_to_csv(item):
    savefileName = "整理后数据.csv"
    headList = []  # 键list
    saveList = []  # 值list

    for k, v in item.items():
        headList.append(k)
        saveList.append(v)
    # 如果文件不存在，创建头
    if os.path.exists(savefileName) == False:
        with open(savefileName, "a", encoding="utf-8-sig", newline="") as csvfile:
            writer = csv.writer(csvfile)
            writer.writerow(headList)
    # 保存item中的数据
    with open(savefileName, "a", encoding="utf-8-sig", newline="") as csvfile:
        writer = csv.writer(csvfile)
        writer.writerow(saveList)

    print(str(item))

df_source = pd.read_excel('./aa.xls', sheet_name=4)
df_target = pd.read_csv('./股票项目1.csv')
df_source = df_source.dropna(axis=0, subset=["证券代码"])


for index, row in df_source.iterrows():
    dict_data = dict()
    dict_data['证券代码'] = str(int(row['证券代码'])).zfill(6) + " "


    dict_data['证券简称'] = row['证券简称']
    dict_data['证监会一级行业名称'] = row['证监会一级行业名称']
    dict_data['证监会二级行业名称'] = row['证监会二级行业名称']
    dict_data['证监会\n（国民经济）\n三级行业名称'] = row['证监会\n（国民经济）\n三级行业名称']
    # dict_data['项目名称'] = row['项目名称']
    dict_data['募投类型'] = row['募投类型']
    # dict_data['投资金额（万元）'] = row['投资金额（万元）']
    dict_data['单个项目投资金额占投资总额比例'] = row['单个项目投资金额占投资总额比例']
    dict_data['证监会\n（国民经济）\n四级行业名称'] = row['证监会\n（国民经济）\n四级行业名称']
    dict_data['申万行业分类一级名称'] = row['申万行业分类一级名称']
    dict_data['申万行业分类二级名称'] = row['申万行业分类二级名称']
    dict_data['申万行业分类三级名称'] = row['申万行业分类三级名称']
    dict_data['成立日期'] = row['成立日期']
    dict_data['所属省份'] = row['所属省份']
    dict_data['所属城市'] = row['所属城市']
    dict_data['证券板块'] = row['证券板块']
    dict_data['首发上市日期'] = row['首发上市日期']
    dict_data['保荐机构'] = row['保荐机构']
    dict_data['保荐代表人'] = row['保荐代表人']
    dict_data['会计师事务所'] = row['会计师事务所']
    dict_data['注册会计师'] = row['注册会计师']
    dict_data['律师事务所'] = row['律师事务所']
    dict_data['签字律师'] = row['签字律师']
    dict_data['首发募集资金\n[单位]万元'] = row['首发募集资金\n[单位]万元']
    dict_data['首发发行费用\n[单位]万元'] = row['首发发行费用\n[单位]万元']
    dict_data['首发承销及保荐费用\n[单位]万元'] = row['首发承销及保荐费用\n[单位]万元']
    dict_data['首发审计及验资费用\n[单位]万元'] = row['首发审计及验资费用\n[单位]万元']
    dict_data['首发律师费用\n[单位]万元'] = row['首发律师费用\n[单位]万元']
    dict_data['首发信息披露费用\n[单位]万元'] = row['首发信息披露费用\n[单位]万元']
    dict_data['首发相关手续费                   [单位]万元'] = row['首发相关手续费                   [单位]万元']
    dict_data['是否变更'] = row['是否变更']
    dict_data['是否延期'] = row['是否延期']
    dict_data['主营业务'] = row['主营业务']

    # 查找并添加项目信息
    if dict_data['证券代码'].find('6') >= 0:
        tmp_code = "sh" + dict_data['证券代码']
    else:
        tmp_code = "sz" + dict_data['证券代码']

    df_value = df_target[df_target['代码'] == tmp_code[:-1]]
    flag = False
    for i, r in df_value.iterrows():
        dict_data['项目名称'] = r['项目']
        dict_data['投资金额（万元）'] = r['金额']
        flag = True
        ditc_to_csv(dict_data)

    if not flag:
        ditc_to_csv(dict_data)

    print(str(dict_data))



